package com.decent.ejfadmin.finance.dao.provider;

import com.decent.ejfadmin.common.bean.Page;
import com.decent.ejfadmin.common.bean.SupplyConsumption;
import com.decent.ejfadmin.common.bean.WithdrawLog;
import com.decent.ejfadmin.common.bean.WithdrawRecord;
import com.decent.ejfadmin.finance.request.RebateUserQueryParam;
import com.decent.ejfadmin.finance.request.WithdrawRecordQueryParam;
import com.decent.ejfadmin.utils.SQLWrapUtils;
import org.apache.commons.lang3.StringUtils;

import java.util.HashMap;
import java.util.Objects;

/**
 * 供货资金管理
 *
 * @author hejunlei
 */
public class SupplyFinanceDaoProvider {
    /**
     * 修改用户金额或者冻结金额
     */
    public String modifyBalance(HashMap<String, Object> param) {
        StringBuffer sql = new StringBuffer();
        sql.append("update yuhuang_supply_balance set version = version + 1 ");
        int status = (int) param.get("status");
        if (status == SupplyConsumption.WITHDRAW_TYPE) {
            sql.append(" ,freezebalance = (-1)*#{money} + freezebalance ");
        } else {
            sql.append(" ,balance = balance + #{money} ");
        }

        sql.append(" where userId = #{balance.userId} and (balance - freezebalance + #{money}) >= 0  ");
        return sql.toString();
    }

    /**
     * 修改佣金金额或者佣金冻结金额
     */
    public String modifyCommisionBalance(HashMap<String, Object> param) {
        StringBuilder sql = new StringBuilder();
        sql.append("update yuhuang_supply_balance set ");
        int status = (int) param.get("status");
        if (status == SupplyConsumption.WITHDRAW_TYPE) {
            sql.append(" ,freeze_commision = (-1)*#{money} + freeze_commision ");
        } else {
            sql.append(" ,commision = commision + #{money} ");
        }

        sql.append(" where userId = #{balance.userId} and (commision - freeze_commision + #{money}) >= 0  ");
        return sql.toString();
    }


    public String queryWithdrawRecordCount(HashMap<String, Object> params) {
        StringBuffer sql = new StringBuffer(200);
        sql.append("select count(1) from yuhuang_withdraw_record ywr  " +
                "   INNER JOIN yuhuang_sup_certificate ysi ON ywr.userId = ysi.user_id  " +
                "   INNER JOIN yuhuang_sup_user ysu ON ysi.user_id = ysu.user_id  " +
                "   INNER JOIN yuhuang_sup_info ysif ON ysif.user_id = ysu.user_id " +
                "   LEFT JOIN yuhuang_withdraw_bank ysb ON ysb.id = ywr.contractId ");
        WithdrawRecordQueryParam param = (WithdrawRecordQueryParam) params.get("param");
        generateWithdrawRecordQueryPageSql(sql, param);
        return sql.toString();
    }

    private void generateWithdrawRecordQueryPageSql(StringBuffer sql, WithdrawRecordQueryParam param) {
        sql.append("where 1=1 ");

        if (null != param.getApplytype()) {
            sql.append("and ywr.applytype = #{param.applytype} ");
        }
        if (param.getTimeType()) {
            if (StringUtils.isNotBlank(param.getStartTime())) {
                sql.append("and ywr.startTime >= #{param.startTime} ");
            }
            if (StringUtils.isNotBlank(param.getEndTime())) {
                sql.append("and ywr.startTime <= #{param.endTime} ");
            }
        } else {
            if (StringUtils.isNotBlank(param.getStartTime())) {
                sql.append("and ywr.endTime >= #{param.startTime} ");
            }
            if (StringUtils.isNotBlank(param.getEndTime())) {
                sql.append("and ywr.endTime <= #{param.endTime} ");
            }
        }
        if (null != param.getPrice()) {
            sql.append("and ywr.price = #{param.price} ");
        }
        if (null != param.getState()) {
            sql.append("and ywr.state = #{param.state} ");
        }
        if (StringUtils.isNotBlank(param.getUserAccount())) {
            sql.append("and (instr(ysu.account,#{param.userAccount})>0 or instr(ysif.name , #{param.userAccount})>0) ");
        }
        if (StringUtils.isNotBlank(param.getOrderId())) {
            sql.append("and ywr.orderId = #{param.orderId} ");
        }
        if (StringUtils.isNotBlank(param.getFlowNo())) {
            sql.append("and ywr.flowNo = #{param.flowNo} ");
        }
        if (StringUtils.isNotBlank(param.getCertificateName())) {
            sql.append("and ysi.`name` = #{param.certificateName} ");
        }
        if (StringUtils.isNotBlank(param.getWithdrawChannel())) {
            sql.append("and ywr.withdrawChannel = #{param.withdrawChannel} ");
        }
        if (StringUtils.isNotBlank(param.getRemark())) {
            sql.append("and instr(ywr.remark,#{param.remark}) >0 ");
        }
        sql.append(" order by ywr.id desc");

    }

    public String queryWithdrawRecordPage(HashMap<String, Object> params) {
        StringBuffer sql = new StringBuffer(300);
        sql.append(" SELECT " +
                " ysb.bank_no bankNo, " +
                " ysb.bank_salt bankSalt, " +
                " ysb.bank_name bankName, " +
                " ywr.id, " +
                " ywr.orderId, " +
                " ywr.flowNo, " +
                " ysu.account userAccount, " +
                " ysif.`name` userName, " +
                " a.`alipay` , " +
                " ysi.`name` certName, " +
                " ysb.bank_name bankName, " +
                " ywr.withdrawChannel withdrawChannel, " +
                " ywr.price, " +
                " ywr.applytype, " +
                " ywr.startTime, " +
                " ywr.endTime, " +
                " ywr.auditstate, " +
                " ywr.state, " +
                " ywr.remark, " +
                " ywr.contractId, " +
                " ywr.withdrawChannel," +
                " ywr.servicePrice," +
                " ywr.payBank " +
                "FROM yuhuang_withdraw_record ywr " +
                "INNER JOIN yuhuang_sup_certificate ysi ON ysi.user_id=ywr.userId " +
                "INNER JOIN yuhuang_sup_user ysu ON ysi.user_id = ysu.user_id " +
                "INNER JOIN yuhuang_sup_info ysif ON ysif.user_id = ysu.user_id " +
                "LEFT JOIN yuhuang_sup_alipay a ON ywr.contractId=a.id AND ywr.withdrawChannel='alipay' " +
                "LEFT JOIN yuhuang_withdraw_bank ysb ON ywr.contractId=ysb.id AND ywr.withdrawChannel='bank' ");
        WithdrawRecordQueryParam param = (WithdrawRecordQueryParam) params.get("param");
        generateWithdrawRecordQueryPageSql(sql, param);
        Page page = param.getPageUnit();
        return SQLWrapUtils.wrapPage(sql, page.getBeginIndex(), page.getSize());
    }


    public String queryRebateUserCount(HashMap<String, Object> params) {
        StringBuffer sql = new StringBuffer(200);
        RebateUserQueryParam param = (RebateUserQueryParam) params.get("param");
        sql.append("select count(1) ");

        generateRebateUserQueryPageSql(sql, param);
        return sql.toString();
    }

    private void generateRebateUserQueryPageSql(StringBuffer sql, RebateUserQueryParam param) {
        sql.append("from yuhuang_sup_user ysu inner join yuhuang_sup_rebate_config ysrc on ysu.user_id = ysrc.userId " +
                "inner join yuhuang_sup_info ysi on ysu.user_id = ysi.user_id " +
                "inner join (SELECT ysu.account recommendAccount,ysi.name recommendName,ysu2.user_id " +
                "FROM yuhuang_sup_user ysu " +
                "inner join yuhuang_sup_user ysu2 on ysu.user_id = ysu2.recommend_id " +
                "inner join yuhuang_sup_info ysi on ysu.user_id = ysi.user_id where 1=1 ");
        if (StringUtils.isNotBlank(param.getRecommendName())) {
            sql.append(" and ysi.name = #{param.recommendName} ");
        }
        if (StringUtils.isNotBlank(param.getRecommendAccount())) {
            sql.append(" and ysu.account = #{param.recommendAccount} ");
        }
        sql.append(" ) userTemp ON userTemp.user_id = ysu.user_id ");
        sql.append("inner join (select userId,sum(case when productType = 1 then settlePrice else 0 end ) mobileTotalFace," +
                "sum(case when productType = 2 then settlePrice else 0 end ) unicomTotalFace," +
                "sum(case when productType = 3 then settlePrice else 0 end ) telecomTotalFace," +
                "sum(case when productType = 300 then settlePrice else 0 end ) zshTotalFace," +
                "sum(case when productType = 310 then settlePrice else 0 end ) zsyTotalFace," +
                "sum(case when productType in(15,16,20) then settlePrice else 0 end ) virtualTotalFace," +
                "sum(settlePrice) totalFace " +
                "from yuhuang_supply_card ");
        if (param.getStartTime() != null) {
            sql.append(" startTime >= #{param.startTime}");
        }
        if (param.getEndTime() != null) {
            sql.append(" endTime <= #{param.endTime} ");
        }
        sql.append(" group by userId )temp on ysu.user_id = temp.userId  where 1=1 ");

        if (StringUtils.isNotBlank(param.getUserAccount())) {
            sql.append("and ysu.account = #{param.userAccount} ");
        }
        if (StringUtils.isNotBlank(param.getUserName())) {
            sql.append("and ysi.name = #{param.userName} ");
        }
    }

    public String queryRebateUserPage(HashMap<String, Object> params) {
        StringBuffer sql = new StringBuffer(300);
        RebateUserQueryParam param = (RebateUserQueryParam) params.get("param");
        sql.append("select ysu.account userAccount,ysi.name userName,userTemp.recommendAccount,userTemp.recommendName," +
                "temp.mobileTotalFace,temp.unicomTotalFace,temp.telecomTotalFace,temp.zshTotalFace,temp.zsyTotalFace,temp.virtualTotalFace," +
                "ysrc.discount," +
                "temp.mobileTotalFace * ysrc.discount mobileRebateTotal,temp.unicomTotalFace * ysrc.discount unicomRebateTotal,temp.telecomTotalFace * ysrc.discount telecomRebateTotal," +
                "temp.zshTotalFace * ysrc.discount zshRebateTotal,temp.zsyTotalFace * ysrc.discount zsyRebateTotal,temp.virtualTotalFace * ysrc.discount virtualRebateTotal," +
                "temp.totalFace * ysrc.discount rebateTotal ");
        generateRebateUserQueryPageSql(sql, param);
        Page page = param.getPageUnit();
        return SQLWrapUtils.wrapPage(sql, page.getBeginIndex(), page.getSize());
    }

    /**
     * 提现失败修改会员余额
     *
     * @param recordMap 参数集合
     * @return
     */
    public String updateTransferFail(HashMap<String, WithdrawRecord> recordMap) {
        WithdrawRecord withdrawRecord = recordMap.get("withdrawRecord");
        StringBuffer sql = new StringBuffer();
        sql.append(" update yuhuang_supply_balance b ");
        sql.append("  set b.freezebalance = b.freezebalance - #{withdrawRecord.price} " +
                " where b.freezebalance - #{withdrawRecord.price} >= 0 " +
                " AND b.userId = #{withdrawRecord.userId} ");

        return sql.toString();
    }

    /**
     * 加款日志
     *
     * @param recordMap
     * @return
     */
    public String addWithdrawLogs(HashMap<String, WithdrawLog> recordMap) {
        WithdrawLog withdrawLog = recordMap.get("withdrawLog");
        StringBuffer sqlKey = new StringBuffer();
        StringBuffer sqlValue = new StringBuffer();
        sqlKey.append(" INSERT INTO yuhuang_withdraw_log ( " +
                " `admin`, " +
                " `withdraw_id`, " +
                " `status`," +
                "  type," +
                "  old_status ");
        if (Objects.nonNull(withdrawLog)) {
            if (StringUtils.isNotBlank(withdrawLog.getRemark())) {
                sqlKey.append(",remark ");
                sqlValue.append(",#{withdrawLog.remark} ");
            }
        }
        sqlKey.append(" ) " +
                "VALUES " +
                " (#{withdrawLog.admin}, #{withdrawLog.withdrawId}, #{withdrawLog.status},#{withdrawLog.type},#{withdrawLog.oldStatus}")
                .append(sqlValue.toString())
                .append(") ");
        return sqlKey.toString();
    }
}
